package ZMJ;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class WordUtil {

	public static void main(String[] args) {

		if (addWord("test", "测试"))
			System.out.println("添加成功！");
		else
			System.out.println("添加失败！");
	}

	private static Connection getConnection() {
		String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		String url = "jdbc:sqlserver://" + "192.168.219.55" + ":1433"
				+ ";DatabaseName=WordBase";
		String userName = "sa";
		String userPwd = "Thanks2015";
		Connection connection = null;
		try {
			try {
				Class.forName(driverName);
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
			connection = DriverManager.getConnection(url, userName, userPwd);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}

	public static boolean addWord(String english, String chinese) {

		Connection conn = null;
		Statement stmt = null;
		int count = 0;
		try {
			conn = getConnection();
			String sql = "insert into t_word(english,chinese,baseid) values('"
					+ english + "','" + chinese + "',1)";
			stmt = conn.createStatement();
			count = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null && !conn.isClosed())
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return count > 0;
	}

	public static boolean delet(String english, String chinese) {

		Connection conn = null;
		Statement stmt = null;
		int count = 0;
		try {
			conn = getConnection();// delete from users where id=1"
			String sql = "delete from t_word where english='" + english
					+ "' and chinese='" + chinese + "'";
			stmt = conn.createStatement();
			count = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null && !conn.isClosed())
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return count > 0;
	}

	public static List<word> getWordList() {
		List<word> list = new ArrayList<>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			String sql = "select * from t_word";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			word w = null;
			while (rs.next()) {
				w = new word(rs.getInt("wordid"), rs.getString("english"),
						rs.getString("chinese"));
				list.add(w);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null && !conn.isClosed())
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;

	}

	/*
	 * public static word getWord(int id){ return null; }
	 */
	public static boolean englishdelet(int id, String english) {

		Connection conn = null;
		Statement stmt = null;
		int count = 0;
		try {
			conn = getConnection();
			String sql = "update t_word set  english = '" + english
					+ "'  where wordid = '" + id + "' ";
			stmt = conn.createStatement();
			count = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null && !conn.isClosed())
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return count > 0;
	}

	public static boolean chinesedelet(int id, String chinese) {

		Connection conn = null;
		Statement stmt = null;
		int count = 0;
		try {
			conn = getConnection();
			String sql = "update t_word set  chinese = '" + chinese
					+ "'  where wordid = '" + id + "' ";
			stmt = conn.createStatement();
			count = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null && !conn.isClosed())
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return count > 0;
	}

}